package com.cn.springcloudshop.common;

import com.cn.springcloudshop.model.YmShopOrder;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author: wuyanzu
 * @Date: 2018/8/3
 */
public class ExcelUtil {

    private static final Logger logger = Logger.getLogger(ExcelUtil.class);

    /**
     *
     * 判断excel版本
     * @author wuyanzu
     * @date 2018/8/3 16:12
     * @param [filePath]
     * @return boolean
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     *
     * 判断excel版本
     * @author wuyanzu
     * @date 2018/8/3 16:12
     * @param [filePath]
     * @return boolean
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
    *
    * 根据文件名生成不同workbook
    * @author wuyanzu
    * @date 2018/8/3 16:14
    * @param [file]
    * @return org.apache.poi.ss.usermodel.Workbook
    */
    public static Workbook createWorkbook(File file) throws IOException {
        if (isExcel2003(file.getPath())) {
            return new HSSFWorkbook(new FileInputStream(file));
        }else if (isExcel2007(file.getPath())) {
            return new XSSFWorkbook(new FileInputStream(file));
        }
        return null;
    }

    /**
     *
     * 根据sheet索引号获取对应的sheet
     * @author wuyanzu
     * @date 2018/8/3 15:19
     * @param [workbook, sheetIndex]
     * @return org.apache.poi.ss.usermodel.Sheet
     */
    public static Sheet getSheet(Workbook workbook, int sheetIndex){
        return workbook.getSheetAt(sheetIndex);
    }

    /**
     *
     * 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true
     * @author wuyanzu
     * @date 2018/8/3 15:27
     * @param [object]
     * @return boolean
     */
    public static boolean isHasValues(Object object){
        Field[] fields = object.getClass().getDeclaredFields();
        boolean flag = false;
        for (int i = 0; i < fields.length; i++) {
            String fieldName = fields[i].getName();
            String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
            Method getMethod;
            try {
                getMethod = object.getClass().getMethod(methodName);
                Object obj = getMethod.invoke(object);
                if (null != obj && "".equals(obj)) {
                    flag = true;
                    break;
                }
            } catch (Exception e) {
                logger.error(e);
            }
        }
        return flag;
    }

    /**
     *
     * 获取单元格格式
     * @author wuyanzu
     * @date 2018/8/3 15:31
     * @param [workbook]
     * @return org.apache.poi.hssf.usermodel.HSSFCellStyle
     */
    public static HSSFCellStyle getCellStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return style;
    }

    /**
     *
     * 生成字体样式
     * @author wuyanzu
     * @date 2018/8/3 15:31
     * @param [workbook]
     * @return org.apache.poi.hssf.usermodel.HSSFFont
     */
    public static HSSFFont getFont(HSSFWorkbook workbook){
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.WHITE.index);
        font.setFontHeightInPoints((short)12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        return font;
    }

    /**
     *
     * 将sheet中的数据保存到list中
     * 1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String
     * 2、页面的file控件name需对应File的文件名
     * @author wuyanzu
     * @date 2018/8/3 15:20
     * @param [vo, file]
     * @return List<Object>
     */
    public static List<Object> importDataFromExcel(Object vo, File file){
        List<Object> list = new ArrayList<Object>();
        Workbook workbook = null;
        try {
            //创建工作簿
            workbook = createWorkbook(file);
            //创建工作表sheet
            Sheet sheet = getSheet(workbook, 0);
            //获取sheet中数据的行数
            int rows = sheet.getPhysicalNumberOfRows();
            //获取表头单元格个数
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            //利用反射，给JavaBean的属性进行赋值
            Field[] fields = vo.getClass().getDeclaredFields();
            for (int i = 1; i < rows; i++) {//第一行为标题栏，从第二行开始取数据
                Row row = sheet.getRow(i);
                int index = 0;
                while (index < cells) {
                    Cell cell = row.getCell(index);
                    if (null == cell) {
                        cell = row.createCell(index);
                    }
                    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String value = null == cell.getStringCellValue()?"":cell.getStringCellValue();
                    Field field = fields[index];
                    String fieldName = field.getName();
                    String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
                    Method setMethod = vo.getClass().getDeclaredMethod(methodName, new Class[]{String.class});
                    setMethod.invoke(vo, new Object[]{value});
                    index++;
                }
                if (isHasValues(vo)) {//判断对象属性是否有值
                    list.add(vo);
                    vo = vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象
                }
            }
        } catch (Exception e) {
            logger.error(e);
        }finally{
            try {
                workbook.close();//关闭流
            } catch (Exception e2) {
                logger.error(e2);
            }
        }
        return list;
    }

    /**
    *
    * 生成Excel
    * @author wuyanzu
    * @date 2018/8/6 14:42
    * @param [list, headers, title, httpServletResponse, httpServletRequest, fileName]
    * @return void
    */
    public static  <T> void exportDataToExcel(List<T> list,String[] headers,String title,HttpServletResponse httpServletResponse,
                                      HttpServletRequest httpServletRequest,String fileName){
        OutputStream os = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽15个字节
        sheet.setDefaultColumnWidth(15);
        //生成一个样式
        HSSFCellStyle style = getCellStyle(workbook);
        //生成一个字体
        HSSFFont font = getFont(workbook);
        //把字体应用到当前样式
        style.setFont(font);
        //生成表格标题
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short)300);
        HSSFCell cell = null;
        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        //将数据放入sheet中
        for (int i = 0; i < list.size(); i++) {
            YmShopOrder order = (YmShopOrder) list.get(i);
            row = sheet.createRow(i+1);
            T t = list.get(i);
            //利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
            Field[] fields = t.getClass().getDeclaredFields();
            try {
                for (int j = 0; j < fields.length; j++) {
                    cell = row.createCell(j);
                    Field field = fields[j];
                    String fieldName = field.getName();
                    String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
                    Method getMethod = t.getClass().getMethod(methodName,new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    if (field.getGenericType().toString().equals("class java.util.Date")) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        value = sdf.format((Date) value);
                    }
                    if(null == value)
                        value ="";
                    cell.setCellValue(value.toString());
                }
            } catch (Exception e) {
                logger.error(e);
            }
        }
        try {
            String agent = httpServletRequest.getHeader("USER-AGENT").toLowerCase();
            httpServletResponse.setContentType("application/vnd.ms-excel");
            String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            if (agent.contains("firefox")) {
                httpServletResponse.setCharacterEncoding("utf-8");
                httpServletResponse.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls" );
            } else {
                httpServletResponse.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
            }
            os = httpServletResponse.getOutputStream();
            workbook.write(os);
        } catch (Exception e) {
            logger.error(e);
        }finally{
            try {
                os.flush();
                os.close();
            } catch (IOException e) {
                logger.error(e);
            }
        }
    }
}
